﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows;
using System.Text.RegularExpressions;
using System.Data.SqlClient;


namespace Models
{
    static public class UpdateDatabaseModel
    {
        static string type1 = "int,float,smallint,bit,bigint,tinyint,smallmoney,sql_variant,text,xml,uniqueidentifer,timestamp,ntext,image,datetime,smalldatetime";
        static string type2 = "char,nchar,varchar,nvarchar,binary,varbinary";
        static string type3 = "decimal,numeric";


        static string default1 = "decimal,numeric,int,float,smallint,bit,bigint,tinyint,smallmoney,sql_variant,text,xml,uniqueidentifer,ntext,image";
        static string default2 = "char,nchar,varchar,nvarchar";
        //static string default3 = "datetime,smalldatetime,timestamp";

        //Tạo SCript Create New table
        static public DataTable CreateTableScript(Models.DataModel conn, DataTable tb)
        { 
            string query = Models.FilesModel.RealAllText("Create_Table_Script.sql");
            string filter = "(";
            string tablename = string.Empty;
            foreach (DataRow row in tb.Rows)
            {
                if (tablename == row["TABLE_NAME"].ToString())
                    continue;
                if (row["NEW"].ToString() != "0") //0: Create New
                    continue;
                tablename = row["TABLE_NAME"].ToString();
                filter += "''" + row["TABLE_NAME"].ToString() + "'',";                    
            }
            filter += "'''')";
            query = string.Format(query, filter,filter.Replace("''","'"));
            return conn.QueryResults(query).Tables[0];
        }

        //Lấy loại column
        static private string GetColumnType(DataRow row)
        {                        
            string s = row["DATA_TYPE"].ToString();
            if (type1.Contains(row["DATA_TYPE"].ToString()))
                s += "";
            else if (type2.Contains(row["DATA_TYPE"].ToString()))
            {
                if (row["CHARACTER_MAXIMUM_LENGTH"].ToString() == "-1")
                    s += "(MAX)";
                else
                    s += "(" + row["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")";
            }
            else if (type3.Contains(row["DATA_TYPE"].ToString()))
                s += "(" + row["NUMERIC_PRECISION"].ToString() + "," + row["NUMERIC_SCALE"].ToString() + ")";
                return s;
        }

        //Tạo Script Update
        static public void CreateUpdateTableScript(Models.DataModel conn, DataTable tb, ref DataTable tbresult)
        {
            //DataTable tbresult = tb.Copy();

            string tablename = string.Empty;
            foreach (DataRow row in tb.Rows)
            {
                //if (row["table_name"].ToString().Contains("x_ph25"))
                //    tb = tb;
                string defaultValue = default1.Contains(row["DATA_TYPE"].ToString()) ? "0" : (default2.Contains(row["DATA_TYPE"].ToString()) ? "''" : "GETDATE()");
                string isnull = row["IS_NULLABLE"].ToString() == "YES" ? "" : " NOT NULL ";

                string script = "Alter Table [{0}] {1} [{2}] {3} {4};"; // nếu Allow Null
                string script2 = @"UPDATE [{0}] SET [{1}] = {3}; 
                                    ALTER TABLE [{0}] ALTER COLUMN [{1}] {2} NOT NULL;";
                
                if (tablename == row["TABLE_NAME"].ToString())
                    continue;
                if (row["NEW"].ToString() == "0")
                    continue;
                if (row["NEW"].ToString() == "1")
                {                    
                    script = string.Format(script, row["TABLE_NAME"].ToString(), "ADD", row["COLUMN_NAME"].ToString(), GetColumnType(row));
                    if (row["IS_NULLABLE"].ToString() == "NO")
                    {
                        tbresult.Rows.Add(script);
                        script = string.Format(script2, row["TABLE_NAME"].ToString(), row["COLUMN_NAME"].ToString(), GetColumnType(row), "DEFAULT " + defaultValue);
                    }
                }
                else
                {
                    script = string.Format(script, row["TABLE_NAME"].ToString(), "ALTER COLUMN", row["COLUMN_NAME"].ToString(), GetColumnType(row) + isnull, row["COLUMN_DEFAULT"].ToString() == "" ? "" : "DEFAULT" + row["COLUMN_DEFAULT"].ToString().Replace('(', ' ').Replace(')', ' '));
                }
                tbresult.Rows.Add(script);
            }
        }


        //Tạo PRIMARY KEY Script Update
        static public void CreateIndexScript(Models.DataModel conn, DataTable tb, ref DataTable tbresult)
        {
            string tablename = string.Empty;
            string script = string.Empty;            
            bool first = true;
            int n = tb.Rows.Count;
            for (int i = 0; i < n; i++)
            {
                if(tb.Rows[i]["is_primary_key"].ToString() == "False")
                {                    
                    script = @"DROP INDEX {0}.{1}; CREATE {2} INDEX {1} ON {0}({3})";
                    script = string.Format(script, tb.Rows[i]["table_name"].ToString(), tb.Rows[i]["index_name"], tb.Rows[i]["type_desc"].ToString(), tb.Rows[i]["column_name"].ToString());
                    tbresult.Rows.Add(script);
                }
                else
                if (tb.Rows[i]["table_name"].ToString() == tablename)
                {
                    script = script.Insert(script.Length - 1, ",[" + tb.Rows[i]["column_name"].ToString() + "]");
                    if (i == n - 1)
                        tbresult.Rows.Add(script);
                }
                else
                {
                    if (!first)
                        tbresult.Rows.Add(script);
                    tablename = tb.Rows[i]["table_name"].ToString();                   
                    script = "ALTER TABLE [{0}] ADD CONSTRAINT {1} PRIMARY KEY {2} ([{3}])";//ALTER TABLE Persons ADD PRIMARY KEY (P_Id)                                      
                    string dellKey = @"ALTER TABLE [{0}] DROP CONSTRAINT {1}; ";
                    script = string.Format(script, tb.Rows[i]["table_name"].ToString(), tb.Rows[i]["index_name"], tb.Rows[i]["type_desc"].ToString(), tb.Rows[i]["column_name"].ToString());
                    if (tb.Rows[i]["NEW"].ToString() != "0")
                    {
                        dellKey = string.Format(dellKey, tb.Rows[i]["table_name"].ToString(), tb.Rows[i]["index_name"].ToString());
                        script = dellKey + script;
                    }
                    if (i == n - 1)
                        tbresult.Rows.Add(script);
                    first = false;
                }
            }

        }

        //Tạo IDENTITY Script Update
        static public void CreateIdentityScript(Models.DataModel conn, DataTable tb, ref DataTable tbresult)
        {
            string tablename = string.Empty;
            foreach (DataRow row in tb.Rows)
            {
                string script = "ALTER TABLE [{0}] DROP COLUMN [{1}]; Alter Table [{0}] ADD [{1}] {2} not null identity{3}";

                if (row["data_type"].ToString() == "int")
                    script = string.Format(script, row["table_name"].ToString(), row["column_name"].ToString(), row["data_type"].ToString(),"(" + row["seed_value"] + "," + row["increment_value"] + ")");
                else
                    script = string.Format(script, row["table_name"].ToString(), row["column_name"].ToString(), row["data_type"].ToString() + "(" + row["max_length"].ToString() + ",0)", "(" + row["seed_value"] + "," + row["increment_value"] + ")");
                tbresult.Rows.Add(script);
            }
        }

        //Update Table
        static public DataTable BeginUpdateTable(DataModel conn, DataTable tb)
        {
            DataTable tbErr = new DataTable();
            tbErr.Columns.Add("Script");
            tbErr.Columns.Add("ErorMessage");
            Views.frmProcessesUpdate frm = new Views.frmProcessesUpdate();
            frm.Show();
            foreach (DataRow row in tb.Rows)
            {
                try
                {
                    frm.message(row[0].ToString());
                    conn.RunExecute(row[0].ToString());
                }
                catch (Exception ex)
                {
                    tbErr.Rows.Add(row[0].ToString(), ex.Message);            
                }
            }
            frm.Close();
            return tbErr;
        }

        //update store
        static public DataTable BeginUpdateStore(DataModel conn1, DataModel conn2, string BeginDate, string EndDate)
        {
            DataTable tb = CompareStoreModel.ProcedureList(conn1, BeginDate, EndDate);
            DataTable tb1 = CompareStoreModel.ProcedureList(conn1);

            DataTable tbErr = new DataTable();
            tbErr.Columns.Add("Script");
            tbErr.Columns.Add("ErorMessage");
            
            Views.frmProcessesUpdate frm = new Views.frmProcessesUpdate();
            frm.message("Please waiting for create update STORE...!");
            frm.Show();

            string query = FilesModel.RealAllText("Get_store.sql");
            string query1 = string.Empty;
            foreach (DataRow row in tb.Rows)
            {
                //if (row["name"].ToString().Contains("CACTPT1-InitTt"))
                //    query = query;
                DataRow[] rows = tb1.Select(" name = '" + row["name"].ToString() + "'");
                if (rows.Length > 0 && rows[0]["name"].Equals(row["modify_date"]))
                    continue;
                query1 = string.Format(query, row["name"]);
                DataTable _tb = conn1.QueryResults(query1).Tables[0];
                string s = string.Empty;
                foreach (DataRow r in _tb.Rows)
                    s += r[0].ToString();

                string Jquery = @"if exists(select * from sys.procedures WHERE name = '{0}' )
                                DROP PROC [{0}]; ";
                try
                {
                    frm.message("Creating Procedure [" + row["name"].ToString() + "]");
                    conn2.RunExecute(string.Format(Jquery, row["name"].ToString()));
                    string temp = s.ToUpper();
                    Regex rx = new Regex("CREATE PROC");
                    MatchCollection match = rx.Matches(temp);
                    if (match.Count <= 1)
                        conn2.RunExecute(s);
                    else
                    { 
                        conn2.RunExecute(s.Substring(0,match[1].Index));
                        int i;
                        for(i=2;i<match.Count;i++)
                            conn2.RunExecute(s.Substring(match[i - 1].Index, match[i].Index - match[i - 1].Index));
                        conn2.RunExecute(s.Substring(match[i-1].Index));
                    }
                }
                catch (Exception ex)
                {
                    tbErr.Rows.Add("Create Procedure [" + row["name"].ToString() +"]", ex.Message);
                    //MessageBox.Show(ex.Message, row[0].ToString());             
                }
            }

            tb = CompareStoreModel.FunctionList(conn1, BeginDate, EndDate);
            foreach (DataRow row in tb.Rows)
            {
                string Jquery = @"if exists(select * from sys.objects WHERE name = '{0}' )
                                DROP FUNCTION [{0}]; ";
                try
                {
                    frm.message("Creating Function [" + row["ObjectName"].ToString() + "]");//ObjectText
                    conn2.RunExecute(string.Format(Jquery, row["ObjectName"].ToString()));
                    conn2.RunExecute(row["ObjectText"].ToString());
                }
                catch (Exception ex)
                {
                    tbErr.Rows.Add("Create function [" + row["ObjectName"].ToString() + "]", ex.Message);
                }
            }
            frm.Close();
            return tbErr;
        }

        //Update dm File
        static public DataTable BeginUpdateDmFile(DataModel conn1, DataModel conn2,string tableName)
        {            
            DataTable tbErr = new DataTable();
            tbErr.Columns.Add("Script");
            tbErr.Columns.Add("ErorMessage");
            string query = string.Empty;
            //Data Source=172.168.10.230;Initial Catalog=FA11;User ID=;Password=Trusted_Connection=yes;
            //Data Source=172.168.10.230;Initial Catalog=FA11;Integrated Security=True
            Views.frmProcessesUpdate frm = new Views.frmProcessesUpdate();
            frm.message("Updating " + tableName +"...");
            frm.Show();

            string connectstring = @"Server={0};UID={1};PWD={2};{3}";
            string[] arr = conn1.conn.ConnectionString.Split(';');
            string server = arr[0].Split('=')[1];
            string UID = string.Empty;
            string PWD = string.Empty;
            string Security = string.Empty;
            if (arr.Length == 5)
            {
                UID = arr[1].Split('=')[1];
                PWD = arr[2].Split('=')[1];
            }
            else
                Security = "Trusted_Connection=yes;";
            if (tableName.ToUpper() == "DMBC")
                query = FilesModel.RealAllText("Update_dmbc.sql");
            else
                query = FilesModel.RealAllText("Update_dmFile.sql");
            query = string.Format(query, string.Format(connectstring, server, UID, PWD, Security),conn1.conn.Database);
            try
            {
                conn2.RunExecute(query);
            }
            catch(Exception ex)
            {
                tbErr.Rows.Add("Update " + tableName, ex.Message);
            }
            frm.Close();
            return tbErr;
        }

        //Update dm file no OpentRowSet
        static public DataTable BeginUpdateDmFile_NoOpenRowSet(DataModel conn1, DataModel conn2)
        {
            DataTable tbErr = new DataTable();
            tbErr.Columns.Add("Script");
            tbErr.Columns.Add("ErorMessage");
            
            Views.frmProcessesUpdate frm = new Views.frmProcessesUpdate();
            frm.message("Please waiting for create update dmFile...!");
            frm.Show();

            DataTable tb1 = conn1.QueryResults("select * from dmfile").Tables[0];
            DataTable tb2 = conn2.QueryResults("select * from dmfile").Tables[0];
            string insert = @"INSERT INTO dmfile(start_up,[path],
					[file_name],
					file_group,
					hash_code,
					file_contain,
					is_zip,
					is_obfuscator,
					date_time,host) VALUES (@start_up,@path,@file_name,@file_group,@hash_code,@file_contain,@is_zip,@is_obfuscator,@date_time,@host)";
            string update = @"UPDATE dmfile 
		                    SET start_up = @start_up,
			                    [path] = @path,
			                    file_group = @file_group,
			                    hash_code = @hash_code,
			                    file_contain = @file_contain,
			                    is_zip = @is_zip,
			                    is_obfuscator = @is_obfuscator,
			                    date_time = @date_time,
			                    host = @host
                            WHERE dmfile.[file_name] = @file_name AND dmfile.hash_code <> @hash_code";
            
            
            foreach (DataRow row in tb1.Rows)
            {
                DataRow[] rows = tb2.Select("file_name = '" + row["file_name"].ToString() + "'");
                if (rows.Length <= 0)
                {
                    try
                    {
                        frm.message("Iserting dmFile [" + row["file_name"].ToString() + "] ...");
                        conn2.conn.Open();
                        SqlCommand cmd = new SqlCommand(insert, conn2.conn);
                        cmd.Parameters.Add("@start_up",SqlDbType.Int).Value = row["start_up"];
                        cmd.Parameters.Add("@path",SqlDbType.NVarChar).Value = row["path"];
                        cmd.Parameters.Add("@file_name",SqlDbType.NVarChar).Value = row["file_name"];
                        cmd.Parameters.Add("@file_group",SqlDbType.NVarChar).Value = row["file_group"];
                        cmd.Parameters.Add("@hash_code",SqlDbType.NVarChar).Value = row["hash_code"];
                        cmd.Parameters.Add("@file_contain", SqlDbType.Image).Value = row["file_contain"];
                        cmd.Parameters.Add("@is_zip",SqlDbType.Int).Value = row["is_zip"];
                        cmd.Parameters.Add("@is_obfuscator",SqlDbType.Int).Value = row["is_obfuscator"];
                        cmd.Parameters.Add("@date_time",SqlDbType.SmallDateTime).Value = row["date_time"];
                        cmd.Parameters.Add("@host",SqlDbType.NVarChar).Value = row["host"];
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        tbErr.Rows.Add("Insert dmFile [" + row["file_name"].ToString() + "]", ex.Message);
                    }
                    finally
                    {
                        conn2.conn.Close();
                    }
                }
                else
                {
                    if (row["hash_code"].Equals(rows[0]["hash_code"]))
                        continue;
                    try
                    {
                        frm.message("Updating dmFile [" + row["file_name"].ToString() + "] ...");
                        conn2.conn.Open();
                        SqlCommand cmd = new SqlCommand(update, conn2.conn);
                        cmd.Parameters.Add("@start_up", SqlDbType.Int).Value = row["start_up"];
                        cmd.Parameters.Add("@path", SqlDbType.NVarChar).Value = row["path"];
                        cmd.Parameters.Add("@file_name", SqlDbType.NVarChar).Value = row["file_name"];
                        cmd.Parameters.Add("@file_group", SqlDbType.NVarChar).Value = row["file_group"];
                        cmd.Parameters.Add("@hash_code", SqlDbType.NVarChar).Value = row["hash_code"];
                        cmd.Parameters.Add("@file_contain", SqlDbType.Image).Value = row["file_contain"];
                        cmd.Parameters.Add("@is_zip", SqlDbType.Int).Value = row["is_zip"];
                        cmd.Parameters.Add("@is_obfuscator", SqlDbType.Int).Value = row["is_obfuscator"];
                        cmd.Parameters.Add("@date_time", SqlDbType.SmallDateTime).Value = row["date_time"];
                        cmd.Parameters.Add("@host", SqlDbType.NVarChar).Value = row["host"];
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        tbErr.Rows.Add("Update dmFile [" + row["file_name"].ToString() + "]", ex.Message);
                    }
                    finally
                    {
                        conn2.conn.Close();
                    }
                }                
            }
            frm.Close();
            return tbErr;
        }

        //update dmbc no OpentRowSet
        static public DataTable BeginUpdateDmBc_NoOpenRowSet(DataModel conn1, DataModel conn2)
        {
            DataTable tbErr = new DataTable();
            tbErr.Columns.Add("Script");
            tbErr.Columns.Add("ErorMessage");

            Views.frmProcessesUpdate frm = new Views.frmProcessesUpdate();
            frm.message("Please waiting for create update dmBc...!");
            frm.Show();

            DataTable tb1 = conn1.QueryResults("select * from dmbc").Tables[0];
            DataTable tb2 = conn2.QueryResults("select * from dmbc").Tables[0];
            string insert = @"INSERT INTO dmbc(ma_file,nhom_bc,
					        mau,
					        lan,
					        report,
					        caption,
					        caption2,
					        title,
					        isuser,
					        [default],
					        filter,
					        script,
					        subreport,
					        refreport,
					        mau_tu_in,
					        nd51,
					        dupdate,
					        [status],
					        rpt_dir,
					        hash_code,
					        rpt_data,
					        host,
					        hidden,
                            so_lien) VALUES (@ma_file,@nhom_bc,
					        @mau,
					        @lan,
					        @report,
					        @caption,
					        @caption2,
					        @title,
					        @isuser,
					        @default,
					        @filter,
					        @script,
					        @subreport,
					        @refreport,
					        @mau_tu_in,
					        @nd51,
					        @dupdate,
					        @status,
					        @rpt_dir,
					        @hash_code,
					        @rpt_data,
					        @host,
					        @hidden,
                            @so_lien)";

            string update = @"UPDATE dmbc 
		                    SET 
                            nhom_bc = @nhom_bc,
					        caption = @caption,
					        caption2 = @caption2,
					        title = @title,
					        isuser = @isuser,
					        [default] = @default,
					        filter = @filter,
					        script = @script,
					        subreport = @subreport,
					        refreport = @refreport,
					        mau_tu_in = @mau_tu_in,
					        nd51 = @nd51,
					        dupdate = @dupdate,
					        [status] = @status,
					        rpt_dir = @rpt_dir,
					        hash_code = @hash_code,
					        rpt_data = @rpt_data,
					        host = @host,
					        hidden = @hidden,
                            so_lien = @so_lien
                        WHERE 
                            dmbc.ma_file = @ma_file 
				            AND dmbc.report = @report
				            AND dmbc.mau = @mau
				            AND dmbc.lan = @lan				
				            AND dmbc.hash_code <> @hash_code";


            foreach (DataRow row in tb1.Rows)
            {
                DataRow[] rows = null;
                try
                {
                 rows = tb2.Select("ma_file like '" + row["ma_file"].ToString() +"' AND report like '"+row["report"].ToString()+"' AND mau = '" + row["mau"].ToString() +"' AND lan = '" + row["lan"].ToString() + "'");
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message,"");
                }
                if (rows.Length <= 0)
                {
                    try
                    {
                        frm.message("Isert dmBc [" + row["ma_file"].ToString() + "] ...");
                        conn2.conn.Open();
                        SqlCommand cmd = new SqlCommand(insert, conn2.conn);
                        cmd.Parameters.Add("@ma_file", SqlDbType.VarChar).Value = row["ma_file"];
                        cmd.Parameters.Add("@nhom_bc", SqlDbType.NVarChar).Value = row["nhom_bc"];
                        cmd.Parameters.Add("@mau", SqlDbType.Int).Value = row["mau"];
                        cmd.Parameters.Add("@lan", SqlDbType.Int).Value = row["lan"];
                        cmd.Parameters.Add("@report", SqlDbType.Char).Value = row["report"];
                        cmd.Parameters.Add("@caption", SqlDbType.NVarChar).Value = row["caption"];
                        cmd.Parameters.Add("@caption2", SqlDbType.NVarChar).Value = row["caption2"];
                        cmd.Parameters.Add("@title", SqlDbType.NVarChar).Value = row["title"];
                        cmd.Parameters.Add("@isuser", SqlDbType.Decimal).Value = row["isuser"];
                        cmd.Parameters.Add("@default", SqlDbType.Decimal).Value = row["default"];
                        cmd.Parameters.Add("@filter", SqlDbType.NVarChar).Value = row["filter"];
                        cmd.Parameters.Add("@script", SqlDbType.NVarChar).Value = row["script"];
                        cmd.Parameters.Add("@subreport", SqlDbType.NVarChar).Value = row["subreport"];
                        cmd.Parameters.Add("@refreport", SqlDbType.NVarChar).Value = row["refreport"];
                        cmd.Parameters.Add("@mau_tu_in", SqlDbType.Int).Value = row["mau_tu_in"];
                        cmd.Parameters.Add("@nd51", SqlDbType.Int).Value = row["nd51"];
                        cmd.Parameters.Add("@dupdate", SqlDbType.VarChar).Value = row["dupdate"];
                        cmd.Parameters.Add("@status", SqlDbType.Int).Value = row["status"];
                        cmd.Parameters.Add("@rpt_dir", SqlDbType.NVarChar).Value = row["rpt_dir"];
                        cmd.Parameters.Add("@hash_code", SqlDbType.VarChar).Value = row["hash_code"];
                        cmd.Parameters.Add("@rpt_data", SqlDbType.Image).Value = row["rpt_data"];
                        cmd.Parameters.Add("@host", SqlDbType.NVarChar).Value = row["host"];
                        cmd.Parameters.Add("@hidden", SqlDbType.Int).Value = row["hidden"];
                        cmd.Parameters.Add("@so_lien", SqlDbType.Int).Value = row["so_lien"];
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        tbErr.Rows.Add("Insert dmbc [" + row["ma_file"].ToString() + "]", ex.Message);
                    }
                    finally
                    {
                        conn2.conn.Close();
                    }
                }
                else
                {
                    if (row["hash_code"].Equals(rows[0]["hash_code"]))
                        continue;
                    try
                    {
                        frm.message("Update dmBc [" + row["ma_file"].ToString() + "] ...");
                        conn2.conn.Open();
                        SqlCommand cmd = new SqlCommand(update, conn2.conn);
                        cmd.Parameters.Add("@ma_file", SqlDbType.VarChar).Value = row["ma_file"];
                        cmd.Parameters.Add("@nhom_bc", SqlDbType.NVarChar).Value = row["nhom_bc"];
                        cmd.Parameters.Add("@mau", SqlDbType.Int).Value = row["mau"];
                        cmd.Parameters.Add("@lan", SqlDbType.Int).Value = row["lan"];
                        cmd.Parameters.Add("@report", SqlDbType.Char).Value = row["report"];
                        cmd.Parameters.Add("@caption", SqlDbType.NVarChar).Value = row["caption"];
                        cmd.Parameters.Add("@caption2", SqlDbType.NVarChar).Value = row["caption2"];
                        cmd.Parameters.Add("@title", SqlDbType.NVarChar).Value = row["title"];
                        cmd.Parameters.Add("@isuser", SqlDbType.Decimal).Value = row["isuser"];
                        cmd.Parameters.Add("@default", SqlDbType.Decimal).Value = row["default"];
                        cmd.Parameters.Add("@filter", SqlDbType.NVarChar).Value = row["filter"];
                        cmd.Parameters.Add("@script", SqlDbType.NVarChar).Value = row["script"];
                        cmd.Parameters.Add("@subreport", SqlDbType.NVarChar).Value = row["subreport"];
                        cmd.Parameters.Add("@refreport", SqlDbType.NVarChar).Value = row["refreport"];
                        cmd.Parameters.Add("@mau_tu_in", SqlDbType.Int).Value = row["mau_tu_in"];
                        cmd.Parameters.Add("@nd51", SqlDbType.Int).Value = row["nd51"];
                        cmd.Parameters.Add("@dupdate", SqlDbType.VarChar).Value = row["dupdate"];
                        cmd.Parameters.Add("@status", SqlDbType.Int).Value = row["status"];
                        cmd.Parameters.Add("@rpt_dir", SqlDbType.NVarChar).Value = row["rpt_dir"];
                        cmd.Parameters.Add("@hash_code", SqlDbType.VarChar).Value = row["hash_code"];
                        cmd.Parameters.Add("@rpt_data", SqlDbType.Image).Value = row["rpt_data"];
                        cmd.Parameters.Add("@host", SqlDbType.NVarChar).Value = row["host"];
                        cmd.Parameters.Add("@hidden", SqlDbType.Int).Value = row["hidden"];
                        cmd.Parameters.Add("@so_lien", SqlDbType.Int).Value = row["so_lien"];
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        tbErr.Rows.Add("Update dmbc [" + row["ma_file"].ToString() + "]", ex.Message);
                    }
                    finally
                    {
                        conn2.conn.Close();
                    }
                }
            }
            frm.Close();
            return tbErr;
        }

        //Update Views
        static public DataTable BeginUpdateViews(DataModel conn1, DataModel conn2)
        {
            DataTable tb1 = CompareViewsModel.GetAllViews(conn1);
            DataTable tb2 = CompareViewsModel.GetAllViews(conn2);

            DataTable tbErr = new DataTable();
            tbErr.Columns.Add("Script");
            tbErr.Columns.Add("ErorMessage");

            Views.frmProcessesUpdate frm = new Views.frmProcessesUpdate();
            frm.message("Please waiting for create update VIEWS...!");
            frm.Show();

            foreach (DataRow row in tb1.Rows)
            {
                //if (row["name"].ToString().Contains("v_x_dmhdct"))
                //    tb1 = tb1;
                frm.message("Creating Views " + row["name"].ToString());
                try
                {
                    DataRow[] rows = tb2.Select("name = '" + row["name"].ToString() + "'");
                    if (rows.Length <= 0)
                    {
                        conn2.RunExecute(row["definition"].ToString());
                    }
                    else
                    {
                        if (row["modify_date"].Equals(rows[0]["modify_date"]))
                            continue;
                        string content = row["definition"].ToString().Replace("create ", "ALTER ");
                        content = content.Replace("CREATE ", "ALTER ");
                        conn2.RunExecute(content);
                    }
                }
                catch(Exception ex)
                {
                    tbErr.Rows.Add("Create Views " + row["name"].ToString(), ex.Message);
                }
            }
            frm.Close();
            return tbErr;
        }
    }
}
//ALTER TABLE Persons ADD PRIMARY KEY (P_Id)